Amazon Redshift ソートキーのテーブル最適化を有効化するビューを作成してみた

石川覚

2024.11.14

AWS事業本部コンサルティング部の石川です。Amazon Redshiftのテーブルのソートキーの選択は、クエリパフォーマンスに大きな影響を与える重要な要素ですが、自動的に設定する機能があります。しかし、この機能を多数のテーブルに対して個別に適用するのは、時間がかかり、エラーが発生しやすい作業です。本ブログでは、この課題に対処するため、テーブルのソートキーを一括で自動設定に変更したり、元の設定に戻したりするためのSQLを生成するビューを紹介します。

ソートキーを自動的に設定する機能とは

Amazon Redshiftの自動テーブル最適化（ATO: Automatic Table Optimization）というセルフチューニング機能があり、その中にテーブルのソートキーを自動的に設定する機能があります。ソートキーの変更は、以下の条件と要素に基づいて適用されます。自動テーブル最適化は、クエリがテーブルとどのように相互作用するかを継続的に監視します。高度な人工知能メソッドを使用して、ソートキーと選択して、クラスターのワークロードパフォーマンスをあわせて最適化します。

しかし、この機能はテーブル1つ1つに適用しなければなりません。そこで、テーブルのソートキーを自動的に設定する機能を一括で有効にしたり、もとの設定に戻せるようにのSQLを生成するためのビューを作成しました。

ソートキーのテーブル自動設定するためのビュー

テーブルのソートキーをバックアップするための SQL を取得するためのビューです。

ソートキーが設定されている既存のテーブルのソートキー設定をバックアップします。ソートキーが設定されているテーブルを後で元のソートキー設定に復元するための SQL を生成します。

v_generate_alter_sortkey_backup_ddl

--DROP VIEW admin.v_generate_alter_sortkey_backup_ddl;
/**********************************************************************************************
Purpose: View to retrieve the DDL for the sortkeys of existing tables.

Notes:   Backup the sortkey settings of existing tables with sortkeys configured.
         Generate SQL to restore tables with sortkeys to their original sortkey settings later.

         The following filters are useful:
           where tablename in ('t1', 't2')     -- only get DDL for specific tables
           where schemaname in ('s1', 's2')    -- only get DDL for specific schemas

History:
2024-11-14 Satoru Ishikawa Created
**********************************************************************************************/
CREATE OR REPLACE VIEW admin.v_generate_alter_sortkey_backup_ddl
AS
SELECT
 table_id
 ,REGEXP_REPLACE (schemaname, '^zzzzzzzz', '') AS schemaname
 ,REGEXP_REPLACE (tablename, '^zzzzzzzz', '') AS tablename
 ,seq
 ,ddl
FROM
 (
 SELECT
  table_id
  ,schemaname
  ,tablename
  ,seq
  ,ddl
 FROM
  (
  SELECT x.table_id::bigint as table_id, x.schemaname AS schemaname, x.tablename AS tablename, seq,
    case when x.min_sort <0 then 'ALTER TABLE ' || x.tablename || ' ALTER INTERLEAVED SORTKEY (' else 'ALTER TABLE ' || x.schemaname || '.' || x.tablename || ' ALTER SORTKEY (' end as ddl 
    FROM (SELECT
      c.oid::bigint as table_id
      ,n.nspname AS schemaname
      ,c.relname AS tablename
      ,499999999 AS seq
      ,min(attsortkeyord) min_sort 
      FROM pg_namespace AS n
      INNER JOIN  pg_class AS c ON n.oid = c.relnamespace
      INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
      WHERE c.relkind = 'r'
      AND abs(a.attsortkeyord) > 0
      AND a.attnum > 0
      group by 1,2,3,4
    ) AS x
  )
  UNION 
  (
    SELECT
   c.oid::bigint as table_id
   ,n.nspname AS schemaname
   ,c.relname AS tablename
   ,500000000 + abs(a.attsortkeyord) AS seq
   ,CASE WHEN abs(a.attsortkeyord) = 1
    THEN '\t' + QUOTE_IDENT(a.attname)
    ELSE '\t, ' + QUOTE_IDENT(a.attname)
    END AS ddl
  FROM  pg_namespace AS n
  INNER JOIN pg_class AS c ON n.oid = c.relnamespace
  INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
  WHERE c.relkind = 'r'
    AND abs(a.attsortkeyord) > 0
    AND a.attnum > 0
  ORDER BY abs(a.attsortkeyord))
  UNION SELECT
   c.oid::bigint as table_id
   ,n.nspname AS schemaname
   ,c.relname AS tablename
   ,599999999 AS seq
   ,'\t);' AS ddl
  FROM pg_namespace AS n
  INNER JOIN  pg_class AS c ON n.oid = c.relnamespace
  INNER JOIN  pg_attribute AS a ON c.oid = a.attrelid
  WHERE c.relkind = 'r'
    AND abs(a.attsortkeyord) > 0
    AND a.attnum > 0
 )
 ORDER BY table_id,schemaname, tablename, seq
;

実行結果の例

dev=# select * from v_generate_alter_sortkey_backup_ddl;
 table_id | schemaname  |                tablename                 |    seq    |                                 ddl                                  
----------+-------------+------------------------------------------+-----------+----------------------------------------------------------------------
   293976 | public      | category                                 | 499999999 | ALTER TABLE category ALTER SORTKEY (
   293976 | public      | category                                 | 500000001 |         catid
   293976 | public      | category                                 | 599999999 |         );
   293979 | public      | date                                     | 499999999 | ALTER TABLE date ALTER SORTKEY (
   293979 | public      | date                                     | 500000001 |         dateid
   293979 | public      | date                                     | 599999999 |         );
   293982 | public      | event                                    | 499999999 | ALTER TABLE event ALTER SORTKEY (
   293982 | public      | event                                    | 500000001 |         dateid
   293982 | public      | event                                    | 599999999 |         );
   293985 | public      | listing                                  | 499999999 | ALTER TABLE listing ALTER SORTKEY (
   293985 | public      | listing                                  | 500000001 |         dateid
   293985 | public      | listing                                  | 599999999 |         );
   293988 | public      | sales                                    | 499999999 | ALTER TABLE sales ALTER SORTKEY (
   293988 | public      | sales                                    | 500000001 |         dateid
   293988 | public      | sales                                    | 599999999 |         );
   293991 | public      | users                                    | 499999999 | ALTER TABLE users ALTER SORTKEY (
   293991 | public      | users                                    | 500000001 |         userid
   293991 | public      | users                                    | 599999999 |         );
   293994 | public      | venue                                    | 499999999 | ALTER TABLE venue ALTER SORTKEY (
   293994 | public      | venue                                    | 500000001 |         venueid
   293994 | public      | venue                                    | 599999999 |         );
   302174 | public      | part                                     | 499999999 | ALTER TABLE part ALTER SORTKEY (
   302174 | public      | part                                     | 500000001 |         p_partkey
   302174 | public      | part                                     | 599999999 |         );
   326738 | public      | testtab                                  | 499999999 | ALTER TABLE testtab ALTER SORTKEY (
   326738 | public      | testtab                                  | 500000001 |         catid
   326738 | public      | testtab                                  | 599999999 |         );
(27 rows)

ソートキーのテーブル「AUTO」設定するためのビュー

テーブルのソートキーを「AUTO」に設定するための DDL を取得するためのビューです。

ソートキーが設定されている既存のテーブルのソートキー設定を「AUTO」に変更するための SQL を生成します。

v_generate_alter_sortkey_auto_ddl

--DROP VIEW admin.v_generate_alter_sortkey_auto_ddl;
/**********************************************************************************************
Purpose: View to retrieve the DDL for setting the sortkey of a table to "AUTO".

Notes:   Set the sortkey to "AUTO" for existing tables that already have sortkeys configured. 
         It is recommended to backup the existing sortkey settings so that you can revert to 
         the original configuration later if needed.

         The following filters are useful:
           where tablename in ('t1', 't2')     -- only get DDL for specific tables
           where schemaname in ('s1', 's2')    -- only get DDL for specific schemas

History:
2024-11-14 Satoru Ishikawa Created
**********************************************************************************************/
CREATE OR REPLACE VIEW public.v_generate_alter_sortkey_auto_ddl
AS
SELECT
 table_id
 ,REGEXP_REPLACE (schemaname, '^zzzzzzzz', '') AS schemaname
 ,REGEXP_REPLACE (tablename, '^zzzzzzzz', '') AS tablename
 ,seq
 ,ddl
FROM
 (
 SELECT
  table_id
  ,schemaname
  ,tablename
  ,seq
  ,ddl
 FROM
  (
  SELECT x.table_id::bigint as table_id, x.schemaname AS schemaname, x.tablename AS tablename, seq,
    case when x.min_sort <0 then 'ALTER TABLE ' || x.tablename || ' ALTER  SORTKEY AUTO;' else 'ALTER TABLE ' || x.tablename || ' ALTER SORTKEY AUTO;' end as ddl 
    FROM (SELECT
      c.oid::bigint as table_id
      ,n.nspname AS schemaname
      ,c.relname AS tablename
      ,499999999 AS seq
      ,min(attsortkeyord) min_sort 
      FROM pg_namespace AS n
      INNER JOIN  pg_class AS c ON n.oid = c.relnamespace
      INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
      WHERE c.relkind = 'r'
      AND abs(a.attsortkeyord) > 0
      AND a.attnum > 0
      group by 1,2,3,4
    ) AS x
  )
 )
 ORDER BY table_id,schemaname, tablename, seq
;

実行結果の例

dev=# select * from v_generate_alter_sortkey_auto_ddl;
 table_id | schemaname  |                tablename                 |    seq    |                                   ddl                                    
----------+-------------+------------------------------------------+-----------+--------------------------------------------------------------------------
   293976 | public      | category                                 | 499999999 | ALTER TABLE category ALTER SORTKEY AUTO;
   293979 | public      | date                                     | 499999999 | ALTER TABLE date ALTER SORTKEY AUTO;
   293982 | public      | event                                    | 499999999 | ALTER TABLE event ALTER SORTKEY AUTO;
   293985 | public      | listing                                  | 499999999 | ALTER TABLE listing ALTER SORTKEY AUTO;
   293988 | public      | sales                                    | 499999999 | ALTER TABLE sales ALTER SORTKEY AUTO;
   293991 | public      | users                                    | 499999999 | ALTER TABLE users ALTER SORTKEY AUTO;
   293994 | public      | venue                                    | 499999999 | ALTER TABLE venue ALTER SORTKEY AUTO;
   302174 | public      | part                                     | 499999999 | ALTER TABLE part ALTER SORTKEY AUTO;
   326738 | public      | testtab                                  | 499999999 | ALTER TABLE testtab ALTER SORTKEY AUTO;
(9 rows)

最後に

Amazon Redshiftの自動テーブル最適化（ATO）は、クエリパフォーマンスを向上させるための重要な機能ですが、個々のテーブルに適用する必要があります。この課題に対処するため、二つのビューを作成しました。１つ目のビュー「v_generate_alter_sortkey_backup_ddl」は、既存のソートキー設定をバックアップし、後で元の設定に戻すためのSQLを生成します。２つ目のビュー「v_generate_alter_sortkey_auto_ddl」は、テーブルのソートキーを「AUTO」に設定するためのDDLを生成します。

これらのビューを使用することで、データベース管理者は複数のテーブルに対して一括でソートキーの自動設定を有効化したり、必要に応じて元の設定に戻したりすることが容易になります。この方法は、大規模なRedshiftクラスターの管理を効率化し、パフォーマンス最適化プロセスを簡素化する上で非常に有用です。ただし、変更を適用する前に、現在の設定をバックアップし、変更の影響を慎重に評価することが重要です。